﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace AliCMS.SubscribeModule.OleDb
{
    public class Tag : OleDbBase, Dal.ITag
    {
        public int GetInnerIDFromGuid(Guid guid)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Tag.ID, MapType(Alias.Tag.ID)).Value = guid;
            Command.CommandText = "SELECT " + MapColumn(Alias.Tag.InnerID) + " FROM " + MapTable(Alias.Objects.Tag) + " WHERE " + MapColumn(Alias.Tag.ID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return 0;
            else
                return (int)result;
        }
        public Guid GetGuidFromInnerID(object id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Tag.InnerID, MapType(Alias.Tag.InnerID)).Value = id;
            Command.CommandText = "SELECT " + MapColumn(Alias.Tag.ID) + " FROM " + MapTable(Alias.Objects.Tag) + " WHERE " + MapColumn(Alias.Tag.InnerID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return Guid.Empty;
            else
                return (Guid)result;
        }
        public Dictionary<string, object> Get(object id)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            StringBuilder sql = new StringBuilder("SELECT TOP 1");
            sql.Append(" " + MapColumn(Alias.Tag.ID));
            sql.Append("," + MapColumn(Alias.Tag.InnerID));
            sql.Append("," + MapColumn(Alias.Tag.Name, Lang));
            sql.Append(" FROM " + MapTable(Alias.Objects.Tag));

            if (id is Guid)
            {
                Command.CommandText = sql + " WHERE " + MapColumn(Alias.Tag.ID) + "=?";

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.Tag.ID, MapType(Alias.Tag.ID)).Value = id;
            }
            else
            {
                Command.CommandText = sql + " WHERE " + MapColumn(Alias.Tag.InnerID) + "=?";

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.Tag.InnerID, MapType(Alias.Tag.InnerID)).Value = id;
            }

            DataReader = Command.ExecuteReader();
            if (!DataReader.HasRows)
            {
                DataReader.Close();
                return null;
            }

            DataReader.Read();
            int i = 0;
            result[Alias.Tag.ID] = DataReader[i++];
            result[Alias.Tag.InnerID] = DataReader[i++];
            result[Alias.Tag.Name] = DataReader[i++];
            DataReader.Close();

            return result;
        }
        public void Add(Dictionary<string, object> item)
        {
            #region 设置RowLang
            item.Add(Alias.Tag.RowLang, "|" + Lang + "|");
            #endregion

            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            Command.Parameters.Clear();
            PrepareParameters(names, values, item);

            Command.CommandText = "INSERT INTO " + MapTable(Alias.Objects.Tag) + "(" + names + ") VALUES(" + values + ")";
            Command.ExecuteNonQuery();
        }
        public void Modify(int id, Dictionary<string, object> changes)
        {
            #region 更新冗余字段
            for (int j = 0; j < 2; j++)
            {
                Command.Parameters.Clear();
                string sets = null;

                if (j == 0)
                {
                    string select = MapColumn(Alias.Company.InnerID) + "," + MapColumn(Alias.Company.TagInnerIDs);
                    if (changes.ContainsKey(Alias.Tag.Name + Lang))
                    {
                        Command.Parameters.Add("@" + Alias.Company.TagNames, MapType(Alias.Company.TagNames)).Value = changes[Alias.Tag.Name + Lang];
                        select += "," + MapColumn(Alias.Company.TagNames);
                        sets = MapColumn(Alias.Company.TagNames) + "=?";
                    }

                    if (Command.Parameters.Count > 0)
                    {
                        OleDbDataAdapter adapter = new OleDbDataAdapter();
                        adapter.SelectCommand = Command;

                        DataTable table = new DataTable();
                        Command.CommandText = "SELECT " + select + " FROM " + MapTable(Alias.Objects.Company) + " WHERE " + MapColumn(Alias.Company.TagInnerIDs) + " LIKE '%|" + id + "|%'";
                        adapter.Fill(table);

                        Command.Parameters.Add("@" + Alias.Company.InnerID, MapType(Alias.Company.InnerID));
                        foreach (DataRow row in table.Rows)
                        {
                            int index = 0;
                            string temp = null;
                            string[] array = null;

                            #region InnerIDs
                            array = row[1].ToString().Split(new char[] { '|' });
                            for (int i = 1; i < array.Length - 1; i++)
                            {
                                if (array[i] == id.ToString())
                                {
                                    index = i;
                                    break;
                                }
                            }
                            #endregion

                            #region Name
                            if (changes.ContainsKey(Alias.Tag.Name + Lang))
                            {
                                temp = null;
                                array = row[MapColumn(Alias.Company.TagNames).TrimStart('[').TrimEnd(']')].ToString().Split(new char[] { '|' });
                                for (int i = 1; i < array.Length - 1; i++)
                                {
                                    if (temp == null)
                                        temp = "|";

                                    if (index == i)
                                        temp += changes[Alias.Tag.Name + Lang] + "|";
                                    else
                                        temp += array[i] + "|";
                                }

                                Command.Parameters["@" + Alias.Company.TagNames].Value = temp;
                            }
                            #endregion

                            Command.Parameters[Command.Parameters.Count - 1].Value = row[0];
                            Command.CommandText = "UPDATE " + MapTable(Alias.Objects.Company) + " SET " + sets + " WHERE " + MapColumn(Alias.Company.InnerID) + "=?";
                            Command.ExecuteNonQuery();
                        }
                    }
                }
                else if (j == 1)
                {
                    string select = MapColumn(Alias.Person.InnerID) + "," + MapColumn(Alias.Person.TagInnerIDs);
                    if (changes.ContainsKey(Alias.Tag.Name + Lang))
                    {
                        Command.Parameters.Add("@" + Alias.Person.TagNames, MapType(Alias.Person.TagNames)).Value = changes[Alias.Tag.Name + Lang];
                        select += "," + MapColumn(Alias.Person.TagNames);
                        sets = MapColumn(Alias.Person.TagNames) + "=?";
                    }

                    if (Command.Parameters.Count > 0)
                    {
                        OleDbDataAdapter adapter = new OleDbDataAdapter();
                        adapter.SelectCommand = Command;

                        DataTable table = new DataTable();
                        Command.CommandText = "SELECT " + select + " FROM " + MapTable(Alias.Objects.Person) + " WHERE " + MapColumn(Alias.Person.TagInnerIDs) + " LIKE '%|" + id + "|%'";
                        adapter.Fill(table);

                        Command.Parameters.Add("@" + Alias.Person.InnerID, MapType(Alias.Person.InnerID));
                        foreach (DataRow row in table.Rows)
                        {
                            int index = 0;
                            string temp = null;
                            string[] array = null;

                            #region InnerIDs
                            array = row[1].ToString().Split(new char[] { '|' });
                            for (int i = 1; i < array.Length - 1; i++)
                            {
                                if (array[i] == id.ToString())
                                {
                                    index = i;
                                    break;
                                }
                            }
                            #endregion

                            #region Name
                            if (changes.ContainsKey(Alias.Tag.Name + Lang))
                            {
                                temp = null;
                                array = row[MapColumn(Alias.Person.TagNames).TrimStart('[').TrimEnd(']')].ToString().Split(new char[] { '|' });
                                for (int i = 1; i < array.Length - 1; i++)
                                {
                                    if (temp == null)
                                        temp = "|";

                                    if (index == i)
                                        temp += changes[Alias.Tag.Name + Lang] + "|";
                                    else
                                        temp += array[i] + "|";
                                }

                                Command.Parameters["@" + Alias.Person.TagNames].Value = temp;
                            }
                            #endregion

                            Command.Parameters[Command.Parameters.Count - 1].Value = row[0];
                            Command.CommandText = "UPDATE " + MapTable(Alias.Objects.Person) + " SET " + sets + " WHERE " + MapColumn(Alias.Person.InnerID) + "=?";
                            Command.ExecuteNonQuery();
                        }
                    }
                }
            }
            #endregion

            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Tag.InnerID, MapType(Alias.Tag.InnerID)).Value = id;

            #region 检查并设置RowLang
            Command.CommandText = "SELECT " + MapColumn(Alias.Tag.RowLang) + " FROM " + MapTable(Alias.Objects.Tag) + " WHERE " + MapColumn(Alias.Tag.InnerID) + "=?";
            string rowLang = Command.ExecuteScalar().ToString();
            if (!rowLang.Contains("|" + Lang + "|"))
                changes.Add(Alias.Tag.RowLang, rowLang + Lang + "|");
            #endregion

            StringBuilder sql = new StringBuilder("UPDATE " + MapTable(Alias.Objects.Tag) + " SET ");
            Command.Parameters.Clear();
            PrepareParameters(sql, changes);
            Command.Parameters.Add("@" + Alias.Tag.InnerID, MapType(Alias.Tag.InnerID)).Value = id;

            sql.Append(" WHERE " + MapColumn(Alias.Tag.InnerID) + "=?");

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public void Delete(int id)
        {
            #region 更新冗余字段
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Tag.InnerID, MapType(Alias.Tag.InnerID)).Value = id;

            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = Command;

            for (int j = 0; j < 2; j++)
            {
                if (j == 0)
                {
                    DataTable table = new DataTable();
                    Command.CommandText = "SELECT " + MapColumn(Alias.Company.InnerID) + "," + MapColumn(Alias.Company.TagNames) + "," + MapColumn(Alias.Company.TagInnerIDs) + " FROM " + MapTable(Alias.Objects.Company) + " WHERE " + MapColumn(Alias.Company.TagInnerIDs) + " LIKE '%|" + id + "|%'";
                    adapter.Fill(table);

                    Command.Parameters.Clear();
                    Command.Parameters.Add("@" + Alias.Company.TagNames, MapType(Alias.Company.TagNames));
                    Command.Parameters.Add("@" + Alias.Company.TagInnerIDs, MapType(Alias.Company.TagInnerIDs));
                    Command.Parameters.Add("@" + Alias.Company.InnerID, MapType(Alias.Company.InnerID));
                    foreach (DataRow row in table.Rows)
                    {
                        string names = null;
                        string innerIDs = null;

                        string[] nameArray = row[1].ToString().Split(new char[] { '|' });
                        string[] innerIDArray = row[2].ToString().Split(new char[] { '|' });
                        for (int i = 1; i < innerIDArray.Length - 1; i++)
                        {
                            if (innerIDArray[i] == id.ToString())
                                continue;

                            if (innerIDs == null)
                            {
                                names = "|";
                                innerIDs = "|";
                            }

                            names += nameArray[i] + "|";
                            innerIDs += innerIDArray[i] + "|";
                        }

                        Command.Parameters[0].Value = names == null ? DBNull.Value : (object)names;
                        Command.Parameters[1].Value = innerIDs == null ? DBNull.Value : (object)innerIDs;
                        Command.Parameters[2].Value = row[0];
                        Command.CommandText = "UPDATE " + MapTable(Alias.Objects.Company) + " SET " + MapColumn(Alias.Company.TagNames) + "=?," + MapColumn(Alias.Company.TagInnerIDs) + "=? WHERE " + MapColumn(Alias.Company.InnerID) + "=?";
                        Command.ExecuteNonQuery();
                    }
                }
                else if (j == 1)
                {
                    DataTable table = new DataTable();
                    Command.CommandText = "SELECT " + MapColumn(Alias.Person.InnerID) + "," + MapColumn(Alias.Person.TagNames) + "," + MapColumn(Alias.Person.TagInnerIDs) + " FROM " + MapTable(Alias.Objects.Person) + " WHERE " + MapColumn(Alias.Person.TagInnerIDs) + " LIKE '%|" + id + "|%'";
                    adapter.Fill(table);

                    Command.Parameters.Clear();
                    Command.Parameters.Add("@" + Alias.Person.TagNames, MapType(Alias.Person.TagNames));
                    Command.Parameters.Add("@" + Alias.Person.TagInnerIDs, MapType(Alias.Person.TagInnerIDs));
                    Command.Parameters.Add("@" + Alias.Person.InnerID, MapType(Alias.Person.InnerID));
                    foreach (DataRow row in table.Rows)
                    {
                        string names = null;
                        string innerIDs = null;

                        string[] nameArray = row[1].ToString().Split(new char[] { '|' });
                        string[] innerIDArray = row[2].ToString().Split(new char[] { '|' });
                        for (int i = 1; i < innerIDArray.Length - 1; i++)
                        {
                            if (innerIDArray[i] == id.ToString())
                                continue;

                            if (innerIDs == null)
                            {
                                names = "|";
                                innerIDs = "|";
                            }

                            names += nameArray[i] + "|";
                            innerIDs += innerIDArray[i] + "|";
                        }

                        Command.Parameters[0].Value = names == null ? DBNull.Value : (object)names;
                        Command.Parameters[1].Value = innerIDs == null ? DBNull.Value : (object)innerIDs;
                        Command.Parameters[2].Value = row[0];
                        Command.CommandText = "UPDATE " + MapTable(Alias.Objects.Person) + " SET " + MapColumn(Alias.Person.TagNames) + "=?," + MapColumn(Alias.Person.TagInnerIDs) + "=? WHERE " + MapColumn(Alias.Person.InnerID) + "=?";
                        Command.ExecuteNonQuery();
                    }
                }
            }
            #endregion

            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.Tag.InnerID, MapType(Alias.Tag.InnerID)).Value = id;

            StringBuilder sql = new StringBuilder("DELETE FROM " + MapTable(Alias.Objects.Tag) + " WHERE " + MapColumn(Alias.Tag.InnerID) + "=?");

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public DataTable List(int page, int pageSize, string condition, string sort)
        {
            DataTable result = new DataTable();
            result.Columns.Add(Alias.Tag.ID, MapCSType(Alias.Tag.ID));
            result.Columns.Add(Alias.Tag.InnerID, MapCSType(Alias.Tag.InnerID));
            result.Columns.Add(Alias.Tag.Name, MapCSType(Alias.Tag.Name));

            #region 条件
            Command.Parameters.Clear();
            string parenthesis = String.Empty;
            string join = String.Empty;
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                where.Append(" WHERE " + Alias.Tag.RowLang + " LIKE '%|" + Lang + "|%' AND ");
                where.Append("(" + condition + ")");

                ParseCondition(where, "a");
            }
            else
            {
                where.Append(" WHERE a." + MapColumn(Alias.Tag.RowLang) + " LIKE '%|" + Lang + "|%'");
            }
            #endregion

            #region 排序
            if (!String.IsNullOrEmpty(sort))
                sort = " ORDER BY " + ParseSort(sort, "a");
            else
                sort = " ORDER BY a." + MapColumn(Alias.Tag.InnerID) + " DESC";
            #endregion

            #region 构造SQL语句
            StringBuilder sql = new StringBuilder("SELECT");
            if (page > 0 && pageSize > 0)
                sql.Append(" TOP " + (page * pageSize));
            sql.Append(" a." + MapColumn(Alias.Tag.ID));
            sql.Append(",a." + MapColumn(Alias.Tag.InnerID));
            sql.Append(",a." + MapColumn(Alias.Tag.Name, Lang));
            sql.Append(" FROM " + parenthesis + MapTable(Alias.Objects.Tag) + " AS a");
            sql.Append(join + where + sort);
            Command.CommandText = sql.ToString();
            #endregion

            #region 执行SQL语句并获取记录
            int curIndex = 0, rowIndex = 0;
            int startIndex = (page - 1) * pageSize;
            DataReader = Command.ExecuteReader();
            while (DataReader.Read())
            {
                if (pageSize != -1)
                {
                    if (curIndex < startIndex)
                    {
                        curIndex++;
                        continue;
                    }

                    if (rowIndex == pageSize)
                        break;
                }

                DataRow row = result.NewRow();
                int i = 0;
                row[Alias.Tag.ID] = DataReader[i++];
                row[Alias.Tag.InnerID] = DataReader[i++];
                row[Alias.Tag.Name] = DataReader[i++];
                result.Rows.Add(row);

                rowIndex++;
            }
            DataReader.Close();
            #endregion

            return result;
        }
        public int GetRecordCount(string condition)
        {
            #region 条件
            Command.Parameters.Clear();
            string parenthesis = String.Empty;
            string join = String.Empty;
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                where.Append(" WHERE " + Alias.Tag.RowLang + " LIKE '%|" + Lang + "|%' AND ");
                where.Append("(" + condition + ")");

                ParseCondition(where, "a");
            }
            else
            {
                where.Append(" WHERE a." + MapColumn(Alias.Tag.RowLang) + " LIKE '%|" + Lang + "|%'");
            }
            #endregion

            Command.CommandText = "SELECT COUNT(*) FROM " + parenthesis + MapTable(Alias.Objects.Tag) + " AS a" + join + where;
            return Convert.ToInt32(Command.ExecuteScalar());
        }
    }
}
